package Database;

use base Class::Accessor;
use strict;
use warnings;
use Carp::Clan;
use Data::Dumper;
use Schema;
use Digest::MD5 qw/md5_hex/;

my $DBCN = 'dbi:mysql:Chatter';
my $DBID = 'root';
my $DBPW = 'password';

sub new {
  my $class = shift;
  my $schema = Schema->connect($DBCN, $DBID, $DBPW, { mysql_enable_utf8 => 1 });
  $schema->storage->dbh->do("SET names utf8");

  my $account = $schema->resultset('Account');
  my $thread = $schema->resultset('Thread');
  my $reply = $schema->resultset('Reply');

  my $self = bless {
    schema  => $schema,
    account => $account,
    thread  => $thread,
    reply   => $reply,
  }, $class;
  return $self;
}
__PACKAGE__->mk_accessors(qw/schema account thread reply/);

# ユーザ追加更新
sub upsertAccount {
  my ($self, $data) = @_;

  # 登録有無確認
  my $account = $self->account->find($data->{accountId});

  if($account){ #update
    $account = $account->update({
      name       => $data->{name},
      email      => $data->{email},
      url        => $data->{url},
      followings => $data->{followings} || 0,
      followers  => $data->{followers}  || 0,
    });

  } else { #insert
    $account = $self->account->create({
      accountId  => $data->{accountId},
      name       => $data->{name},
      email      => $data->{email},
      url        => $data->{url},
      followings => $data->{followings} || 0,
      followers  => $data->{followers}  || 0,
    });
  }
}

# 統計情報更新
sub updateUserStatistics {
  my ($self, $data) = @_;

  # 登録有無確認
  my $account = $self->account->find($data->{accountId});
  if($account){
    $account = $account->update({
      oldest => $data->{oldest} || "",
      latest => $data->{latest} || "",
      count  => $data->{count},
    });
  }
}

sub updateThreadStatistics {
  my ($self, $data) = @_;

  # 登録有無確認
  my $thread = $self->thread->find($data->{threadId});
  if($thread){
    $thread = $thread->update({
      replyCount => $data->{replyCount},
      updated => $data->{updated},
    });
  }
}

# スレッド追加更新
sub upsertThread {
  my ($self, $data) = @_;

  # 登録有無確認
  my $threadId = substr($data->{url}, rindex($data->{url}, "=") + 1);
  my $thread = $self->thread->find($threadId);

  # スレッドは追加のみ、コメントなしは除外（プロファイル更新しました等）
  if(!$thread && $data->{tweet}){ #insert
    eval{
      $data->{tweet} =~ s/<wbr \/><span class="wbr"><\/span>//g,
      $thread = $self->thread->create({
        threadId   => $threadId,
        accountId  => $data->{accountId},
        groupId    => $data->{groupId},
        tweet      => $data->{tweet},
        url        => $data->{url},
        posted     => $data->{created},
      });
    };
    if($@){
      # 当該スレッド発言者、もしくはグループが関連登録されていないエラーは握りつぶす
      if(index($@, "a foreign key constraint fails") != -1){
        # ignore
      } else {
        croak $@;
      }
    }
  }
}

# 返信追加更新
sub upsertReply {
  my ($self, $thread, $data) = @_;

  # 登録有無確認
  my $threadId = substr($thread->{url}, rindex($thread->{url}, "=") + 1);
  my $replyId = md5_hex($threadId.$data->{accountId}.$data->{created});
  my $reply = $self->reply->find($replyId);

  # 追加のみ
  if(!$reply && $data->{tweet}){ #insert
    eval{
      $data->{tweet} =~ s/<wbr \/><span class="wbr"><\/span>//g,
      $reply = $self->reply->create({
        replyId    => $replyId,
        threadId   => $threadId,
        accountId  => $data->{accountId},
        tweet      => $data->{tweet},
        posted     => $data->{created},
      });
    };
    if($@){
      # 当該スレッド発言者、もしくはグループが関連登録されていないエラーは握りつぶす
      if(index($@, "a foreign key constraint fails") != -1){
        # ignore
      } else {
        croak $@;
      }
    }
  }
}

# 直近投稿日の取得
sub getLatestPostdate {
  my ($self, $account) = @_;
  return $self->_getPostdate($account, "desc");
}

# 最古投稿日の取得
sub getOldestPostdate {
  my ($self, $account) = @_;
  return $self->_getPostdate($account, "asc");
}

sub _getPostdate {
  my ($self, $account, $order) = @_;
  croak("Missing user parameter.") unless($account);

  # 直近スレッド
  my $threads = $self->thread->search(
    { accountId => $account->accountId },
    { order_by => { "-$order" => 'posted' }, rows => 1, }
  );
  my $thread = $threads->count ? $threads->next : undef;

  # 直近返信
  my $replies = $self->reply->search(
    { accountId => $account->accountId },
    { order_by => { "-$order" => 'posted' }, rows => 1, }
  );
  my $reply = $replies->count ? $replies->next : undef;

  if($thread && $reply){
    (my $threadDate = $thread->posted) =~ s/[ :\-]//g;
    (my $replyDate = $reply->posted) =~ s/[ :\-]//g;
    if($order eq "asc"){
      return $threadDate < $replyDate ? $thread->posted : $reply->posted;
    } elsif($order eq "desc"){
      return $threadDate > $replyDate ? $thread->posted : $reply->posted;
    } else {
      return undef;
    }
  } elsif($thread && !$reply){
    return $thread->posted;
  } elsif(!$thread && $reply){
    return $reply->posted;
  } else {
    return undef;
  }
}

# ツイート数
sub getNumOfTweet {
  my ($self, $account) = @_;
  croak("Missing user parameter.") unless($account);

  my $threads = $self->thread->search(
    { accountId => $account->accountId },
  );
  my $replies = $self->reply->search(
    { accountId => $account->accountId },
  );

  my $count = 0;
  $count += $threads->count ? $threads->count : 0;
  $count += $replies->count ? $replies->count : 0;
  return $count;
}

1;

